import db from './mysqlHelper.js';
import lsyPay from './common/lsyPay.js';
import logger from './logger.js';
import moment from 'moment';


//平台的主商户编号
const main_merchant_no = '852504140003';


/**
 * 根据充电桩的信息更新充电枪的信息 用于充电桩信息修改时更新充电枪的信息
 * @param {\} station_id 充电桩id
 * @param {*} type  充电桩类型
 * @param {*} guns_num 充电枪数量
 * @returns 
 */
async function updateChargeGuns(sn, type, guns_num, project_id) {
     console.log(`开始更新充电枪信息 sn:${sn}, type:${type}, guns_num:${guns_num}, project_id:${project_id}`);
    //先移除充电桩对应的充电枪信息
    let sql = `delete from guns where sn=?`;
    let params = [sn];
    let res = await db.query(sql, params);
    if (res instanceof Error) {
        logger.error(`删除旧的充电桩信息出错 error :` + res.message, `sn:${sn}`);
        return { code: 0, msg: '删除旧的充电桩信息出错' };
    }
    //采用事务处理的方式插入新的充电桩信息
    let sqls = [];
    // await conn.query(sqls[i].sql, sqls[i].param);
    for (let i = 0; i < guns_num; i++) {
        sqls.push({ sql: `insert into guns(sn,type,no,project_id) values(?,?,?,?)`, param: [sn, type, i + 1, project_id] });
    }
    sql = `select count(*) as count from guns where project_id='${project_id}' and type=${type}`;
    let count = await db.query(sql);
    if (count instanceof Error) {
        logger.error(`查询充电枪数量出错 error :` + count.message, `sn:${sn}`);
        return { code: 0, msg: '查询充电枪数量出错' };
    }
    let num = count[0].count + guns_num;
    if (type == 0) {
        sqls.push({ sql: `update project_charge_ext set total_slow=? where project_id=?`, param: [num, project_id] });
    } else if (type == 1) {
        sqls.push({ sql: `update project_charge_ext set total_fast=? where project_id=?`, param: [num, project_id] });
    } else if (type == 2) {
        sqls.push({ sql: `update project_charge_ext set total_super=? where  project_id=?`, param: [num, project_id] });
    }
    res = await db.transaction(sqls);
    if (res instanceof Error) {
        logger.error(`插入新的充电桩信息出错 error :` + res.message, `sn:${sn}`);
        return { code: 0, msg: '插入新的充电桩信息出错' };
    }
    return { code: 1 };
}

/**
 * 分账成功后写入分账记录表
 * 
 * @param {Array} share_array 分账记录数组
 * @param {String} share_time 分账时间
 * @param {String} share_reason 分账原因
 * @param {String} share_sn 分账单号
 * @param {String} pay_sn 支付单号
 * @return {Object} 返回结果对象
 * @return {number} code 返回码 1表示成功 0表示失败
 */
async function insertBalanceSharingRecord(share_array, share_time, share_reason, share_sn, pay_sn) {
    let sqls = [];
    for (let index = 0; index < share_array.length; index++) {
        const element = share_array[index];
        let data = {
            share_mch_name: element.share_mch_name,
            receive_mch_name: element.receive_mch_name,
            share_amount: element.share_amount,
            share_time: share_time,
            share_reason: share_reason,
            share_sn: share_sn,
            pay_sn: pay_sn,

        };
        sqls.push({ sql: `insert into balance_sharing_record(share_time,share_reason,share_sn,pay_sn,openid,amount,status) values(?,?,?,?,?,?,?)`, param: [share_time, share_reason, share_sn, pay_sn, element.openid, element.amount, 0] });
    }
    let res = await db.transaction(sqls);
    if (res instanceof Error) {
        logger.error(`插入分账记录表出错 error :` + res.message, `share_sn:${share_sn}`);
        return { code: 0, msg: '插入分账记录表出错' };
    }
    return { code: 1 };
}

/**
 * 充电完成自动退款和分账
 * @param {*} phone  当用充电用户的手机号
 * @param {*} consume_amt  消费金额
 * @param {*} income  收入金额 消费金额-手续费
 * @param {*} quantity 总充电量
 * @param {*} id  订单id
 * @param {*} project_id  项目id
 * @param {*} pay_type  付款类型 0:预充值 10停车预充值
 * @returns 
 */
async function autoRefundAndShare(phone, consume_amt, income, quantity, id, project_id, pay_type = 0) {
    //根据手机号查询可退款的预充值付款记录
    console.log(`开始自动退款和分账 phone:${phone}, consume_amt:${consume_amt}, income:${income}, quantity:${quantity}, id:${id}, project_id:${project_id}, pay_type:${pay_type}`);
    let sql = `select project_id,isp_id,pay_sn,pay_amt,plate,openid,path from user_pay_rec where phone='${phone}' 
    and pay_status=10 and pay_type=${pay_type} and project_id='${project_id}' order by create_time desc limit 1`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        logger.error(`查询可退款的预充值付款记录出错 error :` + res.message, `phone:${phone}`);
        return { code: 0, msg: '查询可退款的预充值付款记录出错' };
    }
    if (res.length == 0) {
        logger.error(`没有可退款的预充值付款记录`, `phone:${phone}`);
        return { code: 0, msg: '没有可退款的预充值付款记录' };
    }
    let pay_sn = res[0].pay_sn;
    let pay_amt = res[0].pay_amt;
    let refund_amt = pay_amt - consume_amt
    let sqls = [];
    if (refund_amt > 0) {
        //退款操作
        let create_time = moment().format('YYYY-MM-DD HH:mm:ss');
        let refund_sn = lsyPay.generateTraceNo();
        let refund_res = await lsyPay.refund(refund_sn, refund_amt, pay_sn);
        if (refund_res.code == 1) {
            logger.error(`退款失败`, `phone:${phone}, refund_amt:${refund_amt}, pay_sn:${pay_sn}`);
            return { code: 0, msg: '退款失败' };
        }
        console.log('退款结果', JSON.stringify(refund_res));
        //开始构建事务更新数据库

        let refund_status = 10;
        if (refund_res.code == 2) refund_status = 0;
        //插入退款记录
        let refund_record = {
            phone: phone,
            refund_amt,
            refund_sn,
            refund_status,
            create_time,
            refund_type: 1, // 1:订单结束退款
            project_id: res[0].project_id,
            isp_id: res[0].isp_id,
            plate: res[0].plate,
            openid: res[0].openid,
        }
        if (refund_res.code == 0) {
            refund_record.refund_time = moment().format('YYYY-MM-DD HH:mm:ss');
        }
        console.log('准备退款记录', JSON.stringify(refund_record));
        sqls.push({ sql: `insert into user_refund_rec set ?`, param: [refund_record] });
        //更新付款记录的状态
        if (refund_res.code == 0) {
            sqls.push({ sql: `update user_pay_rec set pay_status=31,refund_sn=? where pay_sn=?`, param: [refund_sn, pay_sn] });
        } else if (refund_res.code == 2) {
            sqls.push({ sql: `update user_pay_rec set pay_status=30,refund_sn=? where pay_sn=?`, param: [refund_sn, pay_sn] });
        }
    } else {
        sqls.push({ sql: `update user_pay_rec set pay_status=40  where pay_sn=?`, param: [pay_sn] });//更新付款记录的状态
    }
    //联表查询项目分账配置表和商户信息表
    if (income <= 0) {
        logger.error(`收入金额小于等于0`, `phone:${phone}, income:${income}  取消分账`);
        res = await db.transaction(sqls);
        if (res instanceof Error) {
            logger.error(`提前自动分账业务写入数据库出错 error :` + res.message, `phone:${phone}`, JSON.stringify(sqls));
            // return { code: 0, msg: '提前写入自动分账业务写入数据库出错' };
        }
        return { code: 1, msg: '退款分账成功' };
    }

    sql = `select project_account_splitting_config.*,merchant.name, merchant.pay_param from project_account_splitting_config join 
    merchant on project_account_splitting_config.split_out_merchant_id=merchant.id where project_id='${res[0].project_id}'  and split_type>=10 `;
    let splitting_configs = await db.query(sql);
    if (splitting_configs instanceof Error) {
        logger.error(`查询项目分账配置表和商户信息表出错 error :` + splitting_configs.message, `phone:${phone}`);
        res = await db.transaction(sqls);
        if (res instanceof Error) {
            logger.error(`提前自动分账业务写入数据库出错 error :` + res.message, `phone:${phone}`, JSON.stringify(sqls));
            //return { code: 0, msg: '提前写入自动分账业务写入数据库出错' };
        }
        return { code: 0, msg: '查询项目分账配置表和商户信息表出错' };
    }
    if (splitting_configs.length == 0) {
        logger.error(`没有分账配置`, `phone:${phone}`);
        res = await db.transaction(sqls);
        if (res instanceof Error) {
            logger.error(`提前自动分账业务写入数据库出错 error :` + res.message, `phone:${phone}`, JSON.stringify(sqls));
            // return { code: 0, msg: '自动分账业务写入数据库出错' };
        }
        return { code: 0, msg: '没有分账配置' };
    }
    let share_array = [];
    let share_amt = 0;
    let share_sn = lsyPay.generateTraceNo();
    //扣除手续费再分账
    // let fee = Math.floor(consume_amt * 0.006);
    // consume_amt = consume_amt - fee;
    for (let index = 0; index < splitting_configs.length; index++) {
        const element = splitting_configs[index];
        let split_out_amount = 0;

        if (element.split_type == 10)//如果分账类型为10 四轮按比例分账
        {
            let split_ratio = element.split_ratio;
            if (split_ratio == null || split_ratio == 0)//检查分账比例是否为0
            {
                logger.error(`分账比例为0`, `phone:${phone}, split_ratio:${split_ratio}`);
                continue;
            } else if (split_ratio > 100) {
                console.log(`分账比例大于100`, `phone:${phone}, split_ratio:${split_ratio} 强制为100`);
                split_ratio = 100;
            }
            split_out_amount = Math.floor(income * split_ratio / 100);
            if (element.split_ceiling != null && element.split_ceiling != 0)//检查分账是否有月限额
            {
                element.split_ceiling = element.split_ceiling * 100;//限额转换为分
                if (element.c_share_amt == null) {
                    element.c_share_amt = 0;
                }

                if (element.c_share_amt < element.split_ceiling) {

                    if (element.c_share_amt + split_out_amount > element.split_ceiling) //如果本次分账超出限额 只限额以内的部分进行分账
                    {
                        split_out_amount = element.split_ceiling - element.c_share_amt;
                    }

                    if (split_out_amount > 0) {
                        //获取当前的月份
                        let month = moment().month() + 1;
                        if (element.current_month != month)//如果当前月份不等于上次分账的月份
                        {
                            //更新分账配置表的当前月份
                            sqls.push({ sql: `update project_account_splitting_config set current_month=? ,c_share_amt=? where id=?`, param: [month, split_out_amount, element.id] });
                        }
                        else {
                            //更新分账配置表的已发分账金额
                            sqls.push({ sql: `update project_account_splitting_config set c_share_amt=c_share_amt+? where id=?`, param: [split_out_amount, element.id] });
                        }
                    }
                    share_array.push({ share_mch: element.pay_param.mch_no, share_amount: split_out_amount });
                    share_amt += split_out_amount;
                    //插入分账记录表
                    if (element.remark == null) element.remark = '';
                    let share_record = {
                        project_id: element.project_id,
                        receive_mch_id: element.split_out_merchant_id,
                        receive_mch_name: element.name,
                        //  receive_mch_no: element.pay_param.mch_no,
                        share_amount: split_out_amount,
                        share_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                        share_reason: `${phone} 订单结束分账 ${element.remark}`,
                        share_sn
                    }
                    sqls.push({ sql: `insert into balance_share_rec set ?`, param: [share_record] });

                } else {
                    logger.error(`分账金额超过月限额`, `phone:${phone}, split_out_amount:${split_out_amount}, split_ceiling:${element.split_ceiling}`);

                }


            } else {
                if (split_out_amount > 0) {
                    share_array.push({ share_mch: element.pay_param.mch_no, share_amount: split_out_amount });
                    share_amt += split_out_amount;
                    //插入分账记录表
                    if (element.remark == null) element.remark = '';
                    let share_record = {
                        project_id: element.project_id,
                        receive_mch_id: element.split_out_merchant_id,
                        receive_mch_name: element.name,
                        //  receive_mch_no: element.pay_param.mch_no,
                        share_amount: split_out_amount,
                        share_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                        share_reason: `${phone} 订单结束分账 ${element.remark}`,
                        share_sn
                    }
                    sqls.push({ sql: `insert into balance_share_rec set ?`, param: [share_record] });
                }
            }

        }
        else if (element.split_type == 11)//如果分账类型为11 四轮按电量分账
        {
            split_out_amount = element.price * quantity;
            if (element.year_limit > 0) {
                if (element.c_share_amt + split_out_amount >= element.year_limit) {
                    console.warn(`分账超过年最大值限制 phone:${phone}, split_out_amount:${split_out_amount}, split_ceiling:${element.split_ceiling}`)
                    split_out_amount = element.year_limit - element.c_share_amt;
                    if (split_out_amount > 0) {
                        let year = moment().year;
                        if (element.current_year != year) {
                            sqls.push({ sql: `update project_account_splitting_config set current_year=? ,c_share_amt=? where id=?`, param: [year, split_out_amount, element.id] });
                        } else {
                            sqls.push({ sql: `update project_account_splitting_config set c_share_amt=c_share_amt+? where id=?`, param: [split_out_amount, element.id] });
                        }
                    }
                }
            }

            if (element.pay_param.mch_no != main_merchant_no && split_out_amount > 0)//如果被分账账号与分出账号一致 只写记录不实际分账
            {
                share_array.push({ share_mch: element.pay_param.mch_no, share_amount: split_out_amount });
                share_amt += split_out_amount;
                //插入分账记录表
                if (element.remark == null) element.remark = '';
                let share_record = {
                    project_id: element.project_id,
                    receive_mch_id: element.split_out_merchant_id,
                    receive_mch_name: element.name,
                    //  receive_mch_no: element.pay_param.mch_no,
                    share_amount: split_out_amount,
                    share_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                    share_reason: `${phone} 订单结束分账 ${element.remark}`,
                    share_sn
                }
                sqls.push({ sql: `insert into balance_share_rec set ?`, param: [share_record] });
            }

        }


    }
    console.log('自动分账业务sql', JSON.stringify(sqls));
    res = await db.transaction(sqls);
    if (res instanceof Error) {
        logger.error(`自动分账业务写入数据库出错 error :` + res.message, `phone:${phone}`, JSON.stringify(sqls));
        return { code: 0, msg: '自动分账业务写入数据库出错' };
    }
    //开始分账操作
    let balancePay_res = await lsyPay.balancePay(share_sn, share_array, share_amt);
    console.log('分账结果', balancePay_res, JSON.stringify(share_array), share_amt);
    return { code: 1, msg: '退款分账成功' };
}



const sqlInjectionPatterns = [
    /--/, // SQL注释
    /(\b(SELECT|INSERT|DELETE|UPDATE|DROP|ALTER|CREATE|EXEC)\b)/i // SQL关键字
];

// 可安全包含特殊字符的字段白名单
const safeFields = ['openid', 'token', 'access_token', 'session_key'];

function sanitizeInput(key, input) {
    if (typeof input !== 'string') return input;
    if (safeFields.includes(key)) return input; // 跳过白名单字段

    for (let pattern of sqlInjectionPatterns) {
        if (pattern.test(input)) {
            throw new Error(`Potential SQL Injection detected in field "${key}"!`);
        }
    }
    return input;
}

function validateRequestParams(params) {
    for (let key in params) {
        if (!params.hasOwnProperty(key)) continue;
        const value = params[key];
        if (typeof value === 'object' && value !== null) {
            validateRequestParams(value); // 递归
        } else {
            sanitizeInput(key, value);
        }
    }
}


export default {
    updateChargeGuns,
    insertBalanceSharingRecord,
    autoRefundAndShare,
    validateRequestParams
}


// autoRefundAndShare('13871094532', 4, 3, 0, '2506051812388711', '202506056698010', 0)